﻿@using OfficeOpenXml

@{
    var db = new GERPContext();
    foreach (var a in db.Assets.Where(o => o.RecordStatus != "D"))
    {
        if (!string.IsNullOrWhiteSpace(a.AssetName) &&
            !string.IsNullOrWhiteSpace(a.Description) &&
            a.Description.StartsWith(a.AssetName))
        {
            a.Description = a.Description.Replace(a.AssetName, "").Trim();
            if (a.Description.StartsWith("-"))
            {
                a.Description = a.Description.Substring(1, a.Description.Length - 1);
            }
            db.Entry(a).State = EntityState.Modified;
        }
    }
    db.SaveChanges();
    
    
    
    // beforce 3
    // 1 ลบการรับเข้า ทั้งหมด ที่มาก่อน 1 ตค 57, ver ลบ receiptdtl
    // 2 ลบล็อต ที่รายการรับถูกลบ
    // 3 Verify Excel
    // 4 Find Receive 
    var receiptIdsตั้งต้น = new List<int> { 1041, 1042, 1043, 1044, 1045, 2045, 2045, 1041 };
    int[] deleteReceiptIds = db.Receiveds.Where(o => o.ReceiveDate < new DateTime(2014, 10, 1) && !receiptIdsตั้งต้น.Contains(o.ReceiveId))
        .Select(o => o.ReceiveId) .ToArray();
        //new int[] { 39, 40, 41, 1042, 1045, 2047, 2048, 3051, 3052, 3053, 3054, 3055, 3056, 3060, 3062, 3063, 1041, 1042 };


    // beforce 3: ทำข้อมูลให้ตรง
    var mไม้บรรทัด12นิ้ว = db.MasResourceItems.Find(841);
    mไม้บรรทัด12นิ้ว.Code = "101019-00007";

    var mน้ำยาลบคำผิดแบบฟิล์ม = db.MasResourceItems.Find(1534);
    mน้ำยาลบคำผิดแบบฟิล์ม.Code = "101022-00003";

    var mอินเด็กซ์คั่นกระดาษ10หยัก = db.MasResourceItems.Find(5098);
    mอินเด็กซ์คั่นกระดาษ10หยัก.Code = "101026-0003";

    db.Entry(mไม้บรรทัด12นิ้ว).State = EntityState.Modified;
    db.Entry(mน้ำยาลบคำผิดแบบฟิล์ม).State = EntityState.Modified;
    db.Entry(mอินเด็กซ์คั่นกระดาษ10หยัก).State = EntityState.Modified;
    db.SaveChanges();
    
    // 1
    foreach (var id in deleteReceiptIds)
    {
        // ลบการรับเข้า
        var model = db.Receiveds.Find(id);
        model.RecordStatus = "D";
        db.Entry(model).State = EntityState.Modified;

        // ลบรายการภายในการรับเข้า
        foreach (var receiptItem in db.ReceiveDtls.Where(o => o.ReceiveId == model.ReceiveId))
        {
            receiptItem.RecordStatus = "D";
            db.Entry(model).State = EntityState.Modified;
        }
    }
    db.SaveChanges();

    foreach (var receiveDtl in db.ReceiveDtls.Include("Received").Where(o => (o.RecordStatus != o.Received.RecordStatus) &&
                                                         (o.RecordStatus == "D" || o.Received.RecordStatus == "D")).ToList())
    {
        receiveDtl.RecordStatus = "D";
        receiveDtl.Received.RecordStatus = "D";
        if (!receiveDtl.UnitId.HasValue)
        {
            receiveDtl.UnitId = db.MasResourceItems.Find(receiveDtl.ResourceItemId).UnitId;
            
        }
        db.Entry(receiveDtl).State = EntityState.Modified;
        db.Entry(receiveDtl.Received).State = EntityState.Modified;
    }
    db.SaveChanges();
    
    foreach (var receiveDtl in db.ReceiveDtls.Include("Received").ToList())
    { 
        if (!receiveDtl.UnitId.HasValue)
        {
            receiveDtl.UnitId = db.MasResourceItems.Find(receiveDtl.ResourceItemId).UnitId;

        }
        db.Entry(receiveDtl).State = EntityState.Modified;
        db.SaveChanges();
    }

    // 2
    foreach (var officeSupplyLot in 
        db.OfficeSuppliesLots.Where(o => o.ReceiveDtl.Received.RecordStatus == "D" ||
                                         o.ReceiveDtl.RecordStatus == "D").ToList())
    {
        officeSupplyLot.RecordStatus = "D";
        db.Entry(officeSupplyLot).State = EntityState.Modified;
        db.SaveChanges();
    }

    foreach (var rcid in receiptIdsตั้งต้น)
    {
        var rc2 = db.Receiveds.Find(rcid);
        rc2.RecordStatus = "U";
        rc2.CreatedWhen = new DateTime(2014,09,01);
        db.Entry(rc2).State = EntityState.Modified;
        db.SaveChanges();
    }

    db.Database.ExecuteSqlCommand("update IcmOfficeSuppliesLot set recordstatus='D' where OfficeSuppliesLotId = 1189");
    
    
    // 3
    FileInfo file = new FileInfo("C:/_Backup/A1.xlsx");
    var resItems = db.MasResourceItems.Include(o => o.MasUnit).Where(o => o.RecordStatus != "D").ToList();
    var resItems2 = db.MasResourceItems.Where(o => o.RecordStatus != "D").ToList();


    var x = db.ReceiveDtls.Include(o => o.MasUnit).Include(o => o.Received)
        .Include(o => o.MasResourceItem.MasResourceSubGroup.MasResourceGroup.MasResourceType)
        .Where(o => o.RecordStatus != "D" &&
                                      o.Received.ReceiveDate < new DateTime(2014, 10, 1)).ToList();
    
    
    using (ExcelPackage pck = new ExcelPackage(file, true))
    {  
        OfficeOpenXml.ExcelWorksheet wsงบ = pck.Workbook.Worksheets[1]; 
        <table border="1">
            <tr>
                <td>Code</td>
                <td>ResCode</td>
                <td>Name</td> 
                <td>Unit</td>
                <td>ResUnit</td>
                <td>Price</td>
                <td>Remain</td>
                <td>พบขม.พื้นฐาน</td>
                <td>รหัสตรง</td>
                <td>หน่วยตรง</td>
                <td>พบการรับ</td>
            </tr> 
            @for (int i = 6; i <= 101; i++)
            {
                string resItemCode = wsงบ.Cells[i, 1].Value + "";
                string resItemName = wsงบ.Cells[i, 2].Value + "";
                string resItemUnit = wsงบ.Cells[i, 3].Value + "";
                if (resItemUnit == "แพ็ค" || resItemUnit == "ห่อ")
                {
                    resItemUnit = "แพค/ห่อ";
                }
                decimal resItemPrice = Convert.ToDecimal(wsงบ.Cells[i, 4].Value);
                int resItemRemain = Convert.ToInt32(wsงบ.Cells[i, 6].Value);
                var resItem = resItems.FirstOrDefault(o => o.Code == resItemCode); 
                bool bSame = resItem != null && resItemCode == resItem.Code;
                bool bFound = resItem != null;
                bool bSameUnit = resItem != null && resItem.MasUnit != null && resItem.MasUnit.Name == resItemUnit;
                bool foundReceiveDtl = x.Any(o => o.ResourceItemId == resItem.ResItemId);
                <tr>
                    <td>@resItemCode</td>
                    <td>@(resItem == null ? "null" : resItem.Code)</td>
                    <td>@resItemName<br/>@(resItem == null ? "null" : resItem.Name)</td> 
                    <td>@resItemUnit</td>
                    <td>@(resItem == null ? "null" : resItem.MasUnit.Name)</td>
                    <td>@resItemPrice</td>
                    <td>@resItemRemain</td>
                    <td>@(bFound ? "พบ" : "ไม่พบ")</td>
                    <td>@(bSame ? "ตรง" : "ไม่ตรง")</td>
                    <td>@(bSameUnit ? "หน่วยตรง" : "หน่วยไม่ตรง")</td>
                    <td>
                        @if (foundReceiveDtl)
                        {
                            @*@:found
                            @:@string.Join(",", x.Where(o => o.ResourceItemId == resItem.ResItemId).Select(o => o.ReceiveDtlId + "," + o.Quantity + o.MasUnit.Name))
                                *@
                            foreach (var rd1 in x.Where(o => o.ResourceItemId == resItem.ResItemId) )
                            {
                                rd1.RecordStatus = "D";
                                db.Entry(rd1).State = EntityState.Modified;
                                db.SaveChanges();
                            }
                        } @{
                            var rd = new KPIS.GERP.Models.ICM.ReceiveDtl();
                            rd.BgSourceId = 1;
                            rd.CreatedBy = 1;
                            rd.CreatedWhen = new DateTime(2014, 09, 01);
                            rd.ReceiveId = 1042;
                            rd.ResourceItemId = resItem.ResItemId;
                            rd.Remark = resItemCode + "-57-001"; // Lotno
                            rd.UnitId = db.MasUnits.First(o => o.Name == resItemUnit).UnitId;
                            rd.ResourceTypeId = 1;
                            rd.Price = (decimal)resItemPrice;
                            rd.Quantity = resItemRemain;
                            rd.Description = "ชื่อสินค้า/วัตถุดิบ: " + resItemName;
                            rd.RecordStatus = "U";
                            db.ReceiveDtls.Add(rd);
                            db.SaveChanges();

                            var ol = new OfficeSuppliesLot();
                            ol.BgSourceId = 1;
                            ol.CreatedBy = 1;
                            ol.CreatedWhen = new DateTime(2014, 09, 01);
                            ol.ReceiveDtlId = rd.ReceiveDtlId;
                            ol.ResItemId = resItem.ResItemId;
                            ol.LotNo = resItemCode + "-57-001";
                            ol.UnitId = db.MasUnits.First(o => o.Name == resItemUnit).UnitId;
                            ol.OfficeSupplyLotStatusId = 83;
                            ol.Price = (decimal)resItemPrice;
                            ol.Quantity = resItemRemain;
                            ol.Description = "ชื่อสินค้า/วัตถุดิบ: " + resItemName;
                            ol.WhId = 12;
                            ol.RecordStatus = "U";
                            db.OfficeSuppliesLots.Add(ol);
                            db.SaveChanges();
                            
                            @: rd.ReceiveDtlId , @ol.Quantity, @ol.MasUnit.Name
                            
                        }
                    </td> 
                </tr> 
            }
        </table> 
    
    
        OfficeOpenXml.ExcelWorksheet wsรายได้ = pck.Workbook.Worksheets[2]; 
        <table border="1">
            <tr>
                <td>Code</td>
                <td>ResCode</td>
                <td>Name</td>
                <td>ResName</td>
                <td>Unit</td>
                <td>ResUnit</td>
                <td>Price</td>
                <td>Remain</td>
                <td>พบ</td>
                <td>ตรง</td>
                <td>หน่วยตรง</td>
                <td>พบการรับ</td>
            </tr> 
        @for (int i = 6;i <= 17;i++)
        {
            string resItemCode = wsรายได้.Cells[i, 1].Value + "";
            string resItemName = wsรายได้.Cells[i, 2].Value + "";
            string resItemUnit = wsรายได้.Cells[i, 3].Value + "";
            if (resItemUnit == "แพ็ค" || resItemUnit == "ห่อ")
            {
                resItemUnit = "แพค/ห่อ";
            }
            decimal resItemPrice = Convert.ToDecimal(wsรายได้.Cells[i, 6].Value);
            int resItemRemain = Convert.ToInt16(wsรายได้.Cells[i, 7].Value);
             MasResourceItem resItem = resItems.FirstOrDefault(o => o.Code == resItemCode);
             bool bSame = resItem != null && resItemCode == resItem.Code;
             bool bFound = resItem != null;
             bool bSameUnit = resItem != null && resItem.MasUnit != null && resItem.MasUnit.Name == resItemUnit;
             bool foundReceiveDtl = x.Any(o => o.ResourceItemId == resItem.ResItemId);
            <tr>
                <td>@resItemCode</td>
                <td>@(resItem == null?"null":resItem.Code)</td>
                <td>@resItemName</td>
                <td>@(resItem == null ? "null" : resItem.Name)</td>
                <td>@resItemUnit</td>
                <td>@(resItem == null || resItem.MasUnit ==null ? "null" : resItem.MasUnit.Name)</td>
                <td>@resItemPrice</td>
                <td>@resItemRemain</td>
                <td>@(bFound ? "พบ" : "ไม่พบ")</td>
                <td>@(bSame ? "ตรง" : "ไม่ตรง")</td>
                <td>@(bSameUnit ? "หน่วยตรง" : "หน่วยไม่ตรง")</td>
                <td>
                    
                    @if (foundReceiveDtl)
{
    @*@:found
    @:@string.Join(",", x.Where(o => o.ResourceItemId == resItem.ResItemId)
    .Select(o => o.ReceiveDtlId + "," + o.Quantity + o.MasUnit.Name))*@
                        
    foreach (var rd1 in x.Where(o => o.ResourceItemId == resItem.ResItemId))
    {
        rd1.RecordStatus = "D";
        db.Entry(rd1).State = EntityState.Modified;
        db.SaveChanges();
    }
                        }


                    @{
    var rd = new KPIS.GERP.Models.ICM.ReceiveDtl();
    rd.BgSourceId = 11;
    rd.CreatedBy = 1;
    rd.CreatedWhen = new DateTime(2014, 09, 01);
    rd.ReceiveId = 1045;
    rd.ResourceItemId = resItem.ResItemId;
    rd.Remark = resItemCode + "-57-001"; // Lotno
    rd.UnitId = db.MasUnits.First(o => o.Name == resItemUnit).UnitId;
    rd.ResourceTypeId = 1;
    rd.Price = (decimal)resItemPrice;
    rd.Quantity = resItemRemain;
    rd.Description = "ชื่อสินค้า/วัตถุดิบ: " + resItemName;
    rd.RecordStatus = "U";
    db.ReceiveDtls.Add(rd);
    db.SaveChanges();

    var ol = new OfficeSuppliesLot();
    ol.BgSourceId = 11;
    ol.CreatedBy = 1;
    ol.CreatedWhen = new DateTime(2014, 09, 01);
    ol.ReceiveDtlId = rd.ReceiveDtlId;
    ol.ResItemId = resItem.ResItemId;
    ol.LotNo = resItemCode + "-57-001";
    ol.UnitId = db.MasUnits.First(o => o.Name == resItemUnit).UnitId;
    ol.OfficeSupplyLotStatusId = 83;
    ol.Price = (decimal)resItemPrice;
    ol.Quantity = resItemRemain;
    ol.Description = "ชื่อสินค้า/วัตถุดิบ: " + resItemName;
    ol.WhId = 12;
    ol.RecordStatus = "U";
                        
    db.OfficeSuppliesLots.Add(ol);
    db.SaveChanges();

    @: rd.ReceiveDtlId , @ol.Quantity, @ol.MasUnit.Name
}

                </td>
            </tr> 
        }
         </table>
    }

}